#!/bin/sh

table_name=ads_trip_engine_data_car_type_statistics_pdi

create_table_sql=$(cat <<EOF
    DROP TABLE IF EXISTS ${KUDU_DATABASE}.${table_name};
    CREATE TABLE IF NOT EXISTS ${KUDU_DATABASE}.${table_name}(
    car_type                                                        string   comment '车辆型号',
    pdt                                                             string   comment '日期',
    speed_avg                                                       int      comment '车速平均值',
    barometric_pressure_avg                                         int      comment '大气压力平均值',
    output_torque_avg                                               int      comment '发动机净输出扭矩平均值',
    friction_torque_avg                                             int      comment '摩擦扭矩平均值',
    rotation_avg                                                    int      comment '发动机转速平均值',
    fuel_consumption_avg                                            int      comment '发动机燃料流量平均值',
    scr_up_nox_avg                                                  int      comment 'SCR上游NOx传感器输出值平均值',
    scr_down_nox_avg                                                int      comment 'SCR下游NOx传感器输出值平均值',
    reagent_allowance_avg                                           int      comment '反应剂余量平均值',
    air_inflow_avg                                                  int      comment '进气量平均值',
    scr_in_avg                                                      int      comment 'SCR入口温度平均值',
    scr_out_avg                                                     int      comment 'SCR出口温度平均值',
    dropout_voltage_avg                                             int      comment 'DPF压差平均值',
    coolant_temperature_avg                                         int      comment '发动机冷却液温度平均值',
    tank_level_avg                                                  int      comment '油箱液位平均值',
    ternary_down_nox_output_avg                                     int      comment '三元催化器下游NOX传感器输出值平均值',
    ternary_up_o2_output_avg                                        int      comment '三元催化器上游氧传感器输出值平均值',
    ternary_down_o2_output_avg                                      int      comment '三元催化器下游氧传感器输出值平均值',
    speed_max                                                       int      comment '车速最大值',
    barometric_pressure_max                                         int      comment '大气压力最大值',
    output_torque_max                                               int      comment '发动机净输出扭矩最大值',
    friction_torque_max                                             int      comment '摩擦扭矩最大值',
    rotation_max                                                    int      comment '发动机转速最大值',
    fuel_consumption_max                                            int      comment '发动机燃料流量最大值',
    scr_up_nox_max                                                  int      comment 'SCR上游NOx传感器输出值最大值',
    scr_down_nox_max                                                int      comment 'SCR下游NOx传感器输出值最大值',
    reagent_allowance_max                                           int      comment '反应剂余量最大值',
    air_inflow_max                                                  int      comment '进气量最大值',
    scr_in_max                                                      int      comment 'SCR入口温度最大值',
    scr_out_max                                                     int      comment 'SCR出口温度最大值',
    dropout_voltage_max                                             int      comment 'DPF压差最大值',
    coolant_temperature_max                                         int      comment '发动机冷却液温度最大值',
    tank_level_max                                                  int      comment '油箱液位最大值',
    ternary_down_nox_output_max                                     int      comment '三元催化器下游NOX传感器输出值最大值',
    ternary_up_o2_output_max                                        int      comment '三元催化器上游氧传感器输出值最大值',
    ternary_down_o2_output_max                                      int      comment '三元催化器下游氧传感器输出值最大值',
    speed_min                                                       int      comment '车速最小值',
    barometric_pressure_min                                         int      comment '大气压力最小值',
    output_torque_min                                               int      comment '发动机净输出扭矩最小值',
    friction_torque_min                                             int      comment '摩擦扭矩最小值',
    rotation_min                                                    int      comment '发动机转速最小值',
    fuel_consumption_min                                            int      comment '发动机燃料流量最小值',
    scr_up_nox_min                                                  int      comment 'SCR上游NOx传感器输出值最小值',
    scr_down_nox_min                                                int      comment 'SCR下游NOx传感器输出值最小值',
    reagent_allowance_min                                           int      comment '反应剂余量最小值',
    air_inflow_min                                                  int      comment '进气量最小值',
    scr_in_min                                                      int      comment 'SCR入口温度最小值',
    scr_out_min                                                     int      comment 'SCR出口温度最小值',
    dropout_voltage_min                                             int      comment 'DPF压差最小值',
    coolant_temperature_min                                         int      comment '发动机冷却液温度最小值',
    tank_level_min                                                  int      comment '油箱液位最小值',
    ternary_down_nox_output_min                                     int      comment '三元催化器下游NOX传感器输出值最小值',
    ternary_up_o2_output_min                                        int      comment '三元催化器上游氧传感器输出值最小值',
    ternary_down_o2_output_min                                      int      comment '三元催化器下游氧传感器输出值最小值',
    ternary_temperature_output_min                                  int      comment '三元催化器温度传感器输出值最小值',
    ternary_temperature_output_max                                  int      comment '三元催化器温度传感器输出值最大值',
    ternary_temperature_output_avg                                  int      comment '三元催化器温度传感器输出值平均值',
    PRIMARY KEY(car_type, pdt)
)
PARTITION BY HASH(car_type, pdt) PARTITIONS 60
STORED AS KUDU
TBLPROPERTIES (
    'kudu.master_addresses' = 'ecs-hscn6-zh-test-hdp01:7051,ecs-hscn6-zh-test-hdp02:7051,ecs-hscn6-zh-test-hdp03:7051',
    'kudu.num_tablet_replicas'='1'
);
EOF
)

impala-shell -i ${IMPALA_HOST} -d ${KUDU_DATABASE} -q "$create_table_sql"
if [ $? -ne 0 ] ; then
  echo "${table_name}表创建失败"
else
  echo "${table_name}表创建成功"
fi